using System;
using System.Collections.Generic;
using System.Linq;
using Model;
using DBUtility;
using MySql.Data.MySqlClient;
using System.Text;

namespace DAL
{
    public class WorkOrderRepository
    {
        /// 添加工单
        public int AddWorkOrder(WorkOrder workOrder)
        {
            string sql = @"INSERT INTO workorder 
                            (ProductId, WorkOrderType, DetailedType, WorkOrderDescription, WorkOrderStatus, 
                             PlannedStartDate, PlannedDueDate, PlannedQuantity, TestProgramNumber, CompanyNumber) 
                           VALUES 
                            (@ProductId, @WorkOrderType, @DetailedType, @WorkOrderDescription, @WorkOrderStatus, 
                             @PlannedStartDate, @PlannedDueDate, @PlannedQuantity, @TestProgramNumber, @CompanyNumber)";

            MySqlParameter[] parameters = {
                new MySqlParameter("@ProductId", workOrder.ProductId),
                new MySqlParameter("@WorkOrderType", workOrder.WorkOrderType),
                new MySqlParameter("@DetailedType", workOrder.DetailedType),
                new MySqlParameter("@WorkOrderDescription", workOrder.WorkOrderDescription),
                new MySqlParameter("@WorkOrderStatus", workOrder.WorkOrderStatus),
                new MySqlParameter("@PlannedStartDate", workOrder.PlannedStartDate),
                new MySqlParameter("@PlannedDueDate", workOrder.PlannedDueDate),
                new MySqlParameter("@PlannedQuantity", workOrder.PlannedQuantity),
                new MySqlParameter("@TestProgramNumber", workOrder.TestProgramNumber),
                new MySqlParameter("@CompanyNumber", workOrder.CompanyNumber)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        /// 删除工单
        public int DeleteWorkOrder(int workOrderId)
        {
            string sql = "DELETE FROM workorder WHERE WorkOrderId = @WorkOrderId";
            MySqlParameter[] parameters = { new MySqlParameter("@WorkOrderId", workOrderId) };
            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        /// 更新工单信息
        public int UpdateWorkOrder(WorkOrder workOrder)
        {
            string sql = @"UPDATE workorder 
                            SET ProductId = @ProductId, 
                                WorkOrderType = @WorkOrderType, 
                                DetailedType = @DetailedType, 
                                WorkOrderDescription = @WorkOrderDescription, 
                                WorkOrderStatus = @WorkOrderStatus, 
                                PlannedStartDate = @PlannedStartDate, 
                                PlannedDueDate = @PlannedDueDate, 
                                PlannedQuantity = @PlannedQuantity, 
                                TestProgramNumber = @TestProgramNumber, 
                                CompanyNumber = @CompanyNumber 
                          WHERE WorkOrderId = @WorkOrderId";

            MySqlParameter[] parameters = {
                new MySqlParameter("@ProductId", workOrder.ProductId),
                new MySqlParameter("@WorkOrderType", workOrder.WorkOrderType),
                new MySqlParameter("@DetailedType", workOrder.DetailedType),
                new MySqlParameter("@WorkOrderDescription", workOrder.WorkOrderDescription),
                new MySqlParameter("@WorkOrderStatus", workOrder.WorkOrderStatus),
                new MySqlParameter("@PlannedStartDate", workOrder.PlannedStartDate),
                new MySqlParameter("@PlannedDueDate", workOrder.PlannedDueDate),
                new MySqlParameter("@PlannedQuantity", workOrder.PlannedQuantity),
                new MySqlParameter("@TestProgramNumber", workOrder.TestProgramNumber),
                new MySqlParameter("@CompanyNumber", workOrder.CompanyNumber),
                new MySqlParameter("@WorkOrderId", workOrder.WorkOrderId)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        /// 查询所有工单
        public List<WorkOrder> GetAllWorkOrders()
        {
            string sql = "SELECT * FROM workorder";
            return DBHelper.ExecuteQuery(sql, null, MapWorkOrder);
        }

        // 根据ID查询单个工单
        public WorkOrder GetWorkOrderById(int workOrderId)
        {
            string sql = "SELECT * FROM workorder WHERE WorkOrderId = @WorkOrderId";
            MySqlParameter[] parameters = { new MySqlParameter("@WorkOrderId", workOrderId) };
            var workOrders = DBHelper.ExecuteQuery(sql, parameters, MapWorkOrder);
            return workOrders.FirstOrDefault();
        }

        //联合查询工单表和产品表
        public List<Dictionary<string, object>> GetAllWorkOrdersWithProduct()
        {
            string sql = @"
            SELECT 
                w.*, 
                p.ProductType, 
                p.TotalProcessId
            FROM 
                workorder w
            LEFT JOIN 
                product p ON w.ProductId = p.ProductId";

            return DBHelper.ExecuteQuery(sql, null, reader =>
            {
                var row = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[reader.GetName(i)] = reader[i];
                }
                return row;
            });
        }

        /// 映射数据库查询结果到 WorkOrder 对象
        private WorkOrder MapWorkOrder(MySqlDataReader reader)
        {
            return new WorkOrder
            {
                WorkOrderId = (int)reader["WorkOrderId"],
                ProductId = (int)reader["ProductId"],
                WorkOrderType = reader["WorkOrderType"].ToString(),
                DetailedType = reader["DetailedType"] is DBNull ? null : reader["DetailedType"].ToString(),
                WorkOrderDescription = reader["WorkOrderDescription"] is DBNull ? null : reader["WorkOrderDescription"].ToString(),
                WorkOrderStatus = reader["WorkOrderStatus"].ToString(),
                PlannedStartDate = (DateTime)reader["PlannedStartDate"],
                PlannedDueDate = (DateTime)reader["PlannedDueDate"],
                PlannedQuantity = (int)reader["PlannedQuantity"],
                TestProgramNumber = (int)reader["TestProgramNumber"],
                CompanyNumber = (int)reader["CompanyNumber"]
            };
        }

        // 带条件的联合查询
        public List<Dictionary<string, object>> GetWorkOrdersWithProductByCondition(
     int? workOrderId,
     int? productId,
     DateTime? startDate,
     DateTime? endDate)
        {
            // 构建基础SQL
            StringBuilder sql = new StringBuilder(@"
        SELECT 
            w.*, 
            p.ProductType, 
            p.TotalProcessId
        FROM 
            workorder w
        LEFT JOIN 
            product p ON w.ProductId = p.ProductId
        WHERE 1=1");

            // 动态添加条件
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            if (workOrderId.HasValue)
            {
                sql.Append(" AND w.WorkOrderId = @WorkOrderId");
                parameters.Add(new MySqlParameter("@WorkOrderId", workOrderId.Value));
            }

            if (productId.HasValue)
            {
                sql.Append(" AND w.ProductId = @ProductId");
                parameters.Add(new MySqlParameter("@ProductId", productId.Value));
            }

            // 添加日期条件
            if (startDate.HasValue)
            {
                sql.Append(" AND w.PlannedStartDate >= @StartDate");
                parameters.Add(new MySqlParameter("@StartDate", startDate.Value));
            }

            if (endDate.HasValue)
            {
                sql.Append(" AND w.PlannedStartDate <= @EndDate");
                parameters.Add(new MySqlParameter("@EndDate", endDate.Value));
            }

            return DBHelper.ExecuteQuery(sql.ToString(), parameters.ToArray(), reader =>
            {
                var row = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[reader.GetName(i)] = reader[i];
                }
                return row;
            });
        }

        //    public int UpdateProductTotalProcessId(int productId, string totalProcessId)
        //    {
        //        string sql = "UPDATE product SET TotalProcessId = @TotalProcessId WHERE ProductId = @ProductId";
        //        MySqlParameter[] parameters = {
        //    new MySqlParameter("@TotalProcessId", totalProcessId),
        //    new MySqlParameter("@ProductId", productId)
        //};
        //        return DBHelper.ExecuteNonQuery(sql, parameters);
        //    }

        public int UpdateTotalProcessId(int workOrderId, int totalProcessId)
        {
            // 首先获取对应的 ProductId
            string getProductIdSql = "SELECT ProductId FROM workorder WHERE WorkOrderId = @WorkOrderId";
            MySqlParameter[] getProductIdParams = { new MySqlParameter("@WorkOrderId", workOrderId) };
            var productIdResult = DBHelper.ExecuteScalar(getProductIdSql, getProductIdParams);

            if (productIdResult == null || productIdResult == DBNull.Value)
            {
                throw new InvalidOperationException("未找到对应的 ProductId");
            }

            int productId = Convert.ToInt32(productIdResult);

            // 更新 product 表中的 TotalProcessId
            string updateSql = @"UPDATE product SET TotalProcessId = @TotalProcessId WHERE ProductId = @ProductId";
            MySqlParameter[] parameters = {
        new MySqlParameter("@TotalProcessId", totalProcessId),
        new MySqlParameter("@ProductId", productId)
    };
            return DBHelper.ExecuteNonQuery(updateSql, parameters);
        }
    }
}